package com.page.demo.dao.impl;

import com.page.demo.dao.CarDao;
import com.page.demo.pojo.Car;
import com.page.demo.pojo.ov.CarAndBrand;
import com.page.demo.pojo.ov.CarAndCategory;
import com.page.demo.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class CarDaoImpl implements CarDao {

    @Override
    public List<Car> queryByPriceDesc() {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select id, car_number carNumber, brand_id brandId, model, color, category_id categoryId, t_comments comments, price, rent, status, useable " +
                "from t_car order by price desc ";
        List<Car> carList = null;
        try {
            carList = queryRunner.query(sql, new BeanListHandler<>(Car.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return carList;
    }

    @Override
    public List<Car> queryByPriceAsc() {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select id, car_number carNumber, brand_id brandId, model, color, category_id categoryId, t_comments comments, price, rent, status, useable " +
                "from t_car order by price asc ";
        List<Car> carList = null;
        try {
            carList = queryRunner.query(sql, new BeanListHandler<>(Car.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return carList;
    }

    @Override
    public List<Car> queryAll() {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select id, car_number carNumber, brand_id brandId, model, color, category_id categoryId, t_comments comments, price, rent, status, useable from t_car";
        List<Car> carList = null;
        try {
            carList = queryRunner.query(sql, new BeanListHandler<>(Car.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return carList;
    }

    @Override
    public List<CarAndBrand> queryCarAndBrand() {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select c.id,c.car_number,b.name,c.model,c.color,c.category_id,c.t_comments,c.price,c.rent,c.status,c.useable,c.brand_id " +
                "from t_car c left join t_brand b on c.brand_id = b.id order by c.brand_id ";
        List<CarAndBrand> brandList = null;
        try {
            brandList = queryRunner.query(sql, resultSet -> {
                /*
                 * 1. 创建一个集合
                 * 2. 遍历结果集
                 * 3. 创建一个对象
                 * 4. 将结果集的数据封装到对象中
                 * 5. 将对象添加到集合中
                 * 6. 返回集合
                 */
                List<CarAndBrand> list = new ArrayList<>();
                while (resultSet.next()) {
                    CarAndBrand carAndBrand = new CarAndBrand(
                            resultSet.getInt("id"),
                            resultSet.getString("car_number"),
                            resultSet.getString("name"),
                            resultSet.getString("model"),
                            resultSet.getString("color"),
                            resultSet.getInt("category_id"),
                            resultSet.getString("t_comments"),
                            resultSet.getDouble("price"),
                            resultSet.getDouble("rent"),
                            resultSet.getInt("status"),
                            resultSet.getInt("useable"),
                            resultSet.getInt("brand_id")
                    );
                    list.add(carAndBrand);
                }
                return list;
            });
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return brandList;
    }

    @Override
    public List<CarAndBrand> queryCarAndBrand(String brand) {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * " +
                "from (select c.id,c.car_number,b.name,c.model,c.color,c.category_id,c.t_comments,c.price,c.rent,c.status,c.useable,c.brand_id " +
                "from t_car c left join t_brand b on c.brand_id = b.id order by c.brand_id) t " +
                "where name like ? ";
        String str = "%" + brand + "%";
        List<CarAndBrand> brandList = null;
        try {
            brandList = queryRunner.query(sql, resultSet -> {
                List<CarAndBrand> list = new ArrayList<>();
                while (resultSet.next()) {
                    CarAndBrand carAndBrand = new CarAndBrand(
                            resultSet.getInt("id"),
                            resultSet.getString("car_number"),
                            resultSet.getString("name"),
                            resultSet.getString("model"),
                            resultSet.getString("color"),
                            resultSet.getInt("category_id"),
                            resultSet.getString("t_comments"),
                            resultSet.getDouble("price"),
                            resultSet.getDouble("rent"),
                            resultSet.getInt("status"),
                            resultSet.getInt("useable"),
                            resultSet.getInt("brand_id")
                    );
                    list.add(carAndBrand);
                }
                return list;
            }, str);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return brandList;
    }

    @Override
    public List<CarAndCategory> queryCarAndCategory() {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select car.id,car_number, brand_id,  model, color, name, t_comments, price, rent, status, useable, category_id " +
                "from t_car car left join t_category cate on car.category_id = cate.id";
        List<CarAndCategory> list;
        try {
            list = queryRunner.query(sql, resultSet -> {
                List<CarAndCategory> list1 = new ArrayList<>();
                while (resultSet.next()) {
                    CarAndCategory carAndCategory = new CarAndCategory(
                            resultSet.getInt("id"),
                            resultSet.getString("car_number"),
                            resultSet.getInt("brand_id"),
                            resultSet.getString("model"),
                            resultSet.getString("color"),
                            resultSet.getString("name"),
                            resultSet.getString("t_comments"),
                            resultSet.getDouble("price"),
                            resultSet.getDouble("rent"),
                            resultSet.getInt("status"),
                            resultSet.getInt("useable"),
                            resultSet.getInt("category_id")
                    );
                    list1.add(carAndCategory);
                }
                return list1;
            });
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public List<CarAndCategory> queryCarAndCategory(String category) {
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * " +
                "from (select car.id,car_number, brand_id,  model, color, name, t_comments, price, rent, status, useable, category_id from t_car car left join t_category cate on car.category_id = cate.id) t\n" +
                "where name like ? ";
        String str = "%" + category + "%";
        List<CarAndCategory> categoryList = null;
        try {
            queryRunner.query(sql, resultSet -> {
                List<CarAndCategory> list1 = new ArrayList<>();
                while (resultSet.next()) {
                    CarAndCategory carAndCategory = new CarAndCategory(
                            resultSet.getInt("id"),
                            resultSet.getString("car_number"),
                            resultSet.getInt("brand_id"),
                            resultSet.getString("model"),
                            resultSet.getString("color"),
                            resultSet.getString("name"),
                            resultSet.getString("t_comments"),
                            resultSet.getDouble("price"),
                            resultSet.getDouble("rent"),
                            resultSet.getInt("status"),
                            resultSet.getInt("useable"),
                            resultSet.getInt("category_id")
                    );
                    list1.add(carAndCategory);
                }
                return list1;
            },str);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return categoryList;
    }
}
